* Statistic for Table 5
* Compute expenditure share on non-tradables for Mexico in 1995
clear
set more off
import delimited using "../Database/OECD/input/MEX1995domimp", clear
drop gdp
gen var = _n // sector id
drop if var==69 | var==70 // these are taxes and the sum of inputs
merge m:1 var using "temp/NT.dta" // sector-level indicator of non-tradable
drop _merge
local jj =1
local names "c01t05 c10t14 c15t16 c17t19 c20 c21t22 c23 c24 c25 c26 c28 c27 c29 c30t33x c31 c34 c35 c36t37 c40t41 c45 c50t52 c55 c60t63 c64 c65t67 c70 c71 c72 c73t74 c75 c80 c85 c90t93 c95 cons gfcf invnt cons_nonres cons_abr expo"

foreach x of local names{
bys NT: egen s`jj' = total(`x') // spending on tradable or non-tradable for each sector (column)
local jj = `jj'+1 // Note: jj=1 to 34 are the industries and jj=35 is consumption, 36 is gross capital formation, 37 is change in inventories, 38 is consumption by nonresidents (exports), 39 is consumption abroad by residents (imports), 40 is exports (cross-border).
} 

* drop original columns; we now have the "sxx" columns
drop c01t05 c10t14 c15t16 c17t19 c20 c21t22 c23 c24 c25 c26 c28 c27 c29 c30t33x c31 c34 c35 c36t37 c40t41 c45 c50t52 c55 c60t63 c64 c65t67 c70 c71 c72 c73t74 c75 c80 c85 c90t93 c95 cons gfcf invnt cons_nonres cons_abr expo
bys NT: gen counter = _n // drop duplicate rows
keep if counter==1
drop counter var v1

* We have so far the total expenditure on each category of NT (i.e., NT=0,1,2,3) by buyer, where the buyers are all the sectors, final consumption, inventories, etc. 
reshape long s, i(NT) j(sector)
sort sector
reshape wide s, i(sector) j(NT)

* Dataset has 40 rows (sectors) and 5 columns (sector id and total spending in each category of NT)
	* Columns: s0, s1, s2, s3 record spending in traded industries, non-traded industries, value added and output
	* Consumption is sector 35

* Compute the aggregate share in NT lumping all buyers togeter
egen totalT = total(s0)	
egen totalNT = total(s1)
keep if sector==1	
gen shareN = totalNT / (totalNT + totalT)
export excel shareN using "`1'/Table5_extra", first(var) sh("NTShare") replace
